﻿CREATE proc [dbo].[sp_getApptTimeSpanByDate] (@date Datetime)
/*
* 统计预约挂号时间分布
*/
as 

begin   

select a.apptdate as [date],a.time1 ,b.time2 ,c.time3 ,d.time4 

from 

	(select convert(varchar,createtime ,23) as apptDate ,COUNT (*) as time1  from ApptOrder where paystate = 1 and CreateTime between  CONVERT(varchar,@date,23) + ' '+ '00:00:00' and  CONVERT(varchar,@date,23) +' '+ '05:59:59' group by convert(varchar,CreateTime,23))a,
	
	(select convert(varchar,createtime ,23) as apptDate ,COUNT (*) as time2  from ApptOrder where paystate = 1 and CreateTime between  CONVERT(varchar,@date,23) + ' '+ '06:00:00' and  CONVERT(varchar,@date,23) +' '+ '11:59:59' group by convert(varchar,CreateTime,23))b,
	
	(select convert(varchar,createtime ,23) as apptDate ,COUNT (*) as time3  from ApptOrder where paystate = 1 and CreateTime between  CONVERT(varchar,@date,23) + ' '+ '12:00:00' and  CONVERT(varchar,@date,23) +' '+ '17:59:59' group by convert(varchar,CreateTime,23))c,
	
	(select convert(varchar,createtime ,23) as apptDate ,COUNT (*) as time4  from ApptOrder where paystate = 1 and CreateTime between  CONVERT(varchar,@date,23) + ' '+ '18:00:00' and  CONVERT(varchar,@date,23) +' '+ '23:59:59' group by convert(varchar,CreateTime,23))d

where a.apptDate = b.apptDate and b.apptDate = c.apptDate and c.apptDate  = d.apptDate

end
